package com.googlecode.bip.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.naming.NamingException;
import org.apache.log4j.Logger;
import com.googlecode.bip.entity.ITopic;
import com.googlecode.bip.entity.Topic;
import cx.ath.choisnet.sql.SimpleQuery;
import cx.ath.choisnet.sql.SimpleUpdate;

/**
 *
 * @author Claude CHOISNET
 */
public class TopicDAO
    extends BipDAO
        implements Listable<ITopic>
{
    private static final long serialVersionUID = 1L;
    private final static transient Logger slogger = Logger.getLogger( TopicDAO.class );

    /**
     * Build TopicDAO
     */
    public TopicDAO()
    {
    }

    @Override //Listable<ITopic>
    public List<ITopic> getList( String sqlQuery ) throws SQLException, NamingException
    {
        return DBHelper.getList( this, getDataSource(), sqlQuery );
    }

    @Override //Listable<ITopic>
    public ITopic newInstance(final ResultSet rSet )
        throws SQLException
    {
        return new Topic().set( rSet );
    }

    /**
     * Returns an @{link ITopic} according to idtopic and idlocal
     *
     * @param idtopic   idtopic has a int
     * @param idlocal   idlocal has a 2 characters String
     * @return an @{link ITopic} according to idtopic and idlocal
     * @throws NamingException
     * @throws SQLException
     */
    public ITopic findTopic( int idtopic, String idlocal )
        throws NamingException, SQLException
    {
        final String sql = "SELECT * FROM `topicsview` WHERE `idtopic`="
            + idtopic
            + " AND `idlocal`='"
            + idlocal
            +"';";

        slogger.debug( "findTopic: " + sql );

        final SimpleQuery query = new SimpleQuery( getDataSource() );

        try {
            ResultSet   rSet  = query.executeQuery( sql );
            boolean     found = rSet.first();

            if( found ) {
                return newInstance( rSet );
                }
            }
        finally {
            SimpleQuery.quietClose( query );
            }

        return null;
    }

    /**
     * Returns all translation for a @{link ITopic}
     *
     * @param idtopic idtopic to find
     * @return a List with translation for the giving idtopic
     * @throws SQLException if any
     * @throws NamingException if any
     */
    public List<ITopic> findTopic( int idtopic )
        throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `topicsview` WHERE `idtopic`="
            + idtopic
            + ";";

        return getList( sql );
    }

    /**
     * Insert a new Topic (must not exist)
     *
     * @param itopic ITopic to insert
     * @return true if topics have bean insert to database
     * @throws NamingException if any
     * @throws SQLException if any
     */
    public boolean insertTopic( final ITopic itopic )
        throws NamingException, SQLException
    {
        Topic topic = Topic.class.cast( itopic );
        final String sql = "CALL add_topic( '"
            + topic.getIdLocal()
            + "', '"
            + topic.getName()
            + "', '"
            + topic.getDescription()
            + "', "
            + topic.getOrder()
            + ", "
            + topic.getParentIdTopic()
            + " );";

        final SimpleQuery query = new SimpleQuery( getDataSource() );

        slogger.info( "insertTopic: " + sql );

        try {
            ResultSet rSet = query.executeQuery( sql );

            if( rSet.first() ) {
                topic.set( rSet );

                return true;
                }
            else {
                slogger.info( "No value in ResultSet: " + rSet );
                return false;
                }
            }
        finally {
            query.quietClose();
            }
    }

    /**
     *
     * @param idtopic
     * @return
     * @throws NamingException
     * @throws SQLException
     */
    public boolean deleteTopic( final int idtopic )
        throws NamingException, SQLException
    {
        SimpleUpdate update = new SimpleUpdate( getDataSource() );

        final String sql1 = "DELETE FROM `topics` WHERE `idtopic`='"
            + idtopic + "';";
        final String sql2 = "DELETE FROM `topics_local` WHERE `idtopic`='"
            + idtopic + "';";

        slogger.debug( "deleteTopic: sql2 = " + sql2 );
        slogger.debug( "deleteTopic: sql1 = " + sql1 );

        try {
            // TODO: improve (roll back)
            /*int x2 =*/ update.doUpdate( sql2 );
            int x1 = update.doUpdate( sql1 );

            if( x1 == 1 /*& x2 == 1*/ ) {
                return true;
                }
            }
        finally {
            SimpleUpdate.quietClose( update );
            }

        return false;
    }

    /**
     *
     * @param itopic
     * @return
     * @throws NamingException
     * @throws SQLException
     */
    public boolean updateTopic( final ITopic itopic )
        throws NamingException, SQLException
    {
        //Topic topic = Topic.class.cast( itopic );
        SimpleUpdate update = new SimpleUpdate( getDataSource() );

        final String sql = "CALL update_topic( "
            + itopic.getIdTopic()
            + ", '"
            + itopic.getIdLocal()
            + "', '"
            + itopic.getName()
            + "', '"
            + itopic.getDescription()
            + "', "
            + itopic.getOrder()
            + ", "
            + itopic.getParentIdTopic()
            + ", "
            + itopic.getIsActive()
            + " );";

        slogger.debug( "updateTopic: " + sql );

        try {
            /*int x =*/ update.doUpdate( sql );

//            if( x == 1 ) {
//                return true;
//                }
//            slogger.warn( "CAN NOT updateTopic: " + x );
            }
        finally {
            SimpleUpdate.quietClose( update );
            }

//        return false;
        return true;
    }

}

